﻿using System;
using Eassets.Layered.Domain;
using Eassets.Layered.Domain.Asset;
using System.Data.SqlClient;
using System.Data;
using System.Collections.ObjectModel;

namespace Eassets.Data.Asset
{
    public class AssetRequisitionDetailData
    {
        /// <summary>
        /// Inserts assetRequisitionDetail.
        /// </summary>
        /// <param name="assetRequisitionDetail"></param>
        public void AddAssetRequisitionDetail(AssetRequisitionDetail assetRequisitionDetail)
        {
            SqlCommand command = new SqlCommand("SP_AddAssetRequisitionDetail"); // Name of the SP
            CommonData commonData = new CommonData();
            commonData.ExecuteNonQuery(ConstructCommand(command, assetRequisitionDetail));
        }

        /// <summary>
        /// Updates assetRequisitionDetail.
        /// </summary>
        /// <param name="assetRequisitionDetail"></param>
        public void ModifyAssetRequisitionDetail(AssetRequisitionDetail assetRequisitionDetail)
        {
            //SqlCommand command = new SqlCommand("SP_ModifyAssetRequisitionDetail"); // Name of the SP
            //command.Parameters.AddWithValue("@AssetRequisitionId", assetRequisitionDetail.AssetRequisitionId);
            // CommonData commonData = new CommonData();commonData.ExecuteNonQuery(ConstructCommand(command, assetRequisitionDetail));

            SqlCommand command = new SqlCommand("SP_ModifyAssetRequisitionDetail"); // Name of the SP
            command.Parameters.AddWithValue("@TransactionId", assetRequisitionDetail.TransactionId);
            command.Parameters.AddWithValue("@AssetId", assetRequisitionDetail.AssetId);
            command.Parameters.AddWithValue("@Status", assetRequisitionDetail.Status);
            command.Parameters.AddWithValue("@Quantity", assetRequisitionDetail.Quantity);
            command.CommandType = CommandType.StoredProcedure;
            CommonData commonData = new CommonData();
            commonData.ExecuteNonQuery(command);
        }

        /// <summary>
        /// GetbyId assetRequisitionDetail.
        /// </summary>
        /// <param name="assetRequisitionId"></param>
        public AssetRequisitionDetail GetByIdAssetRequisitionDetail(long assetRequisitionId)
        {
            SqlCommand command = new SqlCommand("SP_GetAssetRequisitionDetailById");
            command.Parameters.AddWithValue("@AssetRequisitionId", assetRequisitionId);
            command.CommandType = CommandType.StoredProcedure;
            CommonData commonData = new CommonData();
            SqlDataReader dataReader = commonData.ExecuteReader(command);
            AssetRequisitionDetail assetRequisitionDetail = new AssetRequisitionDetail();
            while (dataReader.Read())
            {
                if (!(String.IsNullOrEmpty(dataReader["AssetRequisitionId"].ToString())))
                {
                    assetRequisitionDetail.AssetRequisitionId = Convert.ToInt64(dataReader["AssetRequisitionId"]);
                }
                if (!(String.IsNullOrEmpty(dataReader["TransactionId"].ToString())))
                {
                    assetRequisitionDetail.TransactionId = Convert.ToInt64(dataReader["TransactionId"]);
                }
                if (!(String.IsNullOrEmpty(dataReader["RequestingOrganizationLevelId"].ToString())))
                {
                    assetRequisitionDetail.RequestingOrganizationLevelId =
                        Convert.ToInt64(dataReader["RequestingOrganizationLevelId"]);
                }
                if (!(String.IsNullOrEmpty(dataReader["IssuingOrganizationLevelId"].ToString())))
                {
                    assetRequisitionDetail.IssuingOrganizationLevelId =
                        Convert.ToInt64(dataReader["IssuingOrganizationLevelId"]);
                }
                if (!(String.IsNullOrEmpty(dataReader["RequestingEmpId"].ToString())))
                {
                    assetRequisitionDetail.RequestingEmpId = Convert.ToInt64(dataReader["RequestingEmpId"]);
                }
                if (!(String.IsNullOrEmpty(dataReader["AssetId"].ToString())))
                {
                    assetRequisitionDetail.AssetId = Convert.ToInt64(dataReader["AssetId"]);
                }
                if (!(String.IsNullOrEmpty(dataReader["Quantity"].ToString())))
                {
                    assetRequisitionDetail.Quantity = Convert.ToInt16(dataReader["Quantity"]);
                }
                if (!(String.IsNullOrEmpty(dataReader["Status"].ToString())))
                {
                    assetRequisitionDetail.Status = Convert.ToString(dataReader["Status"]);
                }
                if (!(String.IsNullOrEmpty(dataReader["UnitOfMeasure"].ToString())))
                {
                    assetRequisitionDetail.UnitOfMeasure = Convert.ToString(dataReader["UnitOfMeasure"]);
                }
                if (!(String.IsNullOrEmpty(dataReader["TransactionDate"].ToString())))
                {
                    assetRequisitionDetail.TransactionDate = Convert.ToDateTime(dataReader["TransactionDate"]);
                }
            }
            dataReader.Close();
            return assetRequisitionDetail;
        }

        /// <summary>
        /// GetAll assetRequisitionDetail.
        /// </summary>
        public Collection<AssetRequisitionDetail> GetAllAssetRequisitionDetail()
        {
            Collection<AssetRequisitionDetail> assetRequisitionDetails = new Collection<AssetRequisitionDetail>();
            SqlCommand command = new SqlCommand("SP_GetAllAssetRequisitionDetails");
            command.CommandType = CommandType.StoredProcedure;
            CommonData commonData = new CommonData();
            SqlDataReader dataReader = commonData.ExecuteReader(command);
            AssetRequisitionDetail assetRequisitionDetail;
            while (dataReader.Read())
            {
                assetRequisitionDetail = new AssetRequisitionDetail();
                if (!(String.IsNullOrEmpty(dataReader["AssetRequisitionId"].ToString())))
                {
                    assetRequisitionDetail.AssetRequisitionId = Convert.ToInt64(dataReader["AssetRequisitionId"]);
                }
                if (!(String.IsNullOrEmpty(dataReader["TransactionId"].ToString())))
                {
                    assetRequisitionDetail.TransactionId = Convert.ToInt64(dataReader["TransactionId"]);
                }

                TransactionData transactionData = new TransactionData();
                assetRequisitionDetail.Transaction =
                    transactionData.GetByIdTransaction(assetRequisitionDetail.TransactionId);

                if (!(String.IsNullOrEmpty(dataReader["RequestingOrganizationLevelId"].ToString())))
                {
                    assetRequisitionDetail.RequestingOrganizationLevelId =
                        Convert.ToInt64(dataReader["RequestingOrganizationLevelId"]);
                }
                if (!(String.IsNullOrEmpty(dataReader["IssuingOrganizationLevelId"].ToString())))
                {
                    assetRequisitionDetail.IssuingOrganizationLevelId =
                        Convert.ToInt64(dataReader["IssuingOrganizationLevelId"]);
                }
                if (!(String.IsNullOrEmpty(dataReader["RequestingEmpId"].ToString())))
                {
                    assetRequisitionDetail.RequestingEmpId = Convert.ToInt64(dataReader["RequestingEmpId"]);
                }
                if (!(String.IsNullOrEmpty(dataReader["AssetId"].ToString())))
                {
                    assetRequisitionDetail.AssetId = Convert.ToInt64(dataReader["AssetId"]);
                }

                Assets asset = new Assets();
                AssetData assetData = new AssetData();
                assetRequisitionDetail.Assets = assetData.GetByIdAsset(assetRequisitionDetail.AssetId);

                if (!(String.IsNullOrEmpty(dataReader["Quantity"].ToString())))
                {
                    assetRequisitionDetail.Quantity = Convert.ToInt16(dataReader["Quantity"]);
                }
                if (!(String.IsNullOrEmpty(dataReader["Status"].ToString())))
                {
                    assetRequisitionDetail.Status = Convert.ToString(dataReader["Status"]);
                }
                if (!(String.IsNullOrEmpty(dataReader["UnitOfMeasure"].ToString())))
                {
                    assetRequisitionDetail.UnitOfMeasure = Convert.ToString(dataReader["UnitOfMeasure"]);
                }
                if (!(String.IsNullOrEmpty(dataReader["TransactionDate"].ToString())))
                {
                    assetRequisitionDetail.TransactionDate = Convert.ToDateTime(dataReader["TransactionDate"]);
                }

                assetRequisitionDetails.Add(assetRequisitionDetail);
            }
            dataReader.Close();
            return assetRequisitionDetails;
        }

        /// <summary>
        /// Get asset by asset code
        /// </summary>
        /// <param name="assetCode"></param>
        /// <returns></returns>
        public long GetAssetIdByAssetCode(string assetCode)
        {
            long id = 0;
            SqlCommand command = new SqlCommand("SP_GetAssetIdByAssetCode");
            command.Parameters.AddWithValue("@AssetCode", assetCode);
            command.CommandType = CommandType.StoredProcedure;
            CommonData commonData = new CommonData();
            SqlDataReader dataReader = commonData.ExecuteReader(command);
            while (dataReader.Read())
            {
                if (!(String.IsNullOrEmpty((dataReader["AssetId"].ToString()))))
                {
                    id = Convert.ToInt64(dataReader["AssetId"]);
                }
            }
            return id;
        }

        /// <summary>
        /// Get assets which are included in authorization groups
        /// </summary>
        /// <returns></returns>
        public Collection<Employee> GetEmployeesInAuthorizationGroups()
        {
            Collection<Employee> employees = new Collection<Employee>();
            SqlCommand command = new SqlCommand("SP_GetEmployeesInAuthorizationGroups");
            command.CommandType = CommandType.StoredProcedure;
            CommonData commonData = new CommonData();
            SqlDataReader dataReader = commonData.ExecuteReader(command);
            Employee employee;
            while (dataReader.Read())
            {
                employee = new Employee();
                if (!(String.IsNullOrEmpty(dataReader["EmployeeName"].ToString())))
                {
                    employee.EmployeeName = Convert.ToString(dataReader["EmployeeName"]);
                }
                if (!(String.IsNullOrEmpty(dataReader["Email"].ToString())))
                {
                    employee.Email = Convert.ToString(dataReader["Email"]);
                }
                employees.Add(employee);
            }
            dataReader.Close();
            return employees;
        }

        /// <summary>
        /// Get asset requisition note details by transaction number
        /// </summary>
        /// <param name="transactionNo"></param>
        /// <returns></returns>
        public Collection<AssetRequisitionDetail> GetARNDetailsByTransactionNo(string transactionNo)
        {
            Collection<AssetRequisitionDetail> assetRequisitionDetailsList = new Collection<AssetRequisitionDetail>();
            SqlCommand command = new SqlCommand("SP_GetARNDetailsByTransactionNo");
            command.Parameters.AddWithValue("@TransactionNo", transactionNo);
            command.CommandType = CommandType.StoredProcedure;
            CommonData commonData = new CommonData();
            SqlDataReader dataReader = commonData.ExecuteReader(command);
            AssetRequisitionDetail assetRequisitionDetail;
            while (dataReader.Read())
            {
                assetRequisitionDetail = new AssetRequisitionDetail();


                if (!(String.IsNullOrEmpty(dataReader["AssetId"].ToString())))
                {
                    assetRequisitionDetail.AssetId = Convert.ToInt16(dataReader["AssetId"]);
                }
                AssetData assetData = new AssetData();
                assetRequisitionDetail.Assets = assetData.GetByIdAsset(assetRequisitionDetail.AssetId);

                if (!(String.IsNullOrEmpty(dataReader["Quantity"].ToString())))
                {
                    assetRequisitionDetail.Quantity = Convert.ToInt16(dataReader["Quantity"]);
                }
                if (!(String.IsNullOrEmpty(dataReader["UnitOfMeasure"].ToString())))
                {
                    assetRequisitionDetail.UnitOfMeasure = Convert.ToString(dataReader["UnitOfMeasure"]);
                }
                assetRequisitionDetailsList.Add(assetRequisitionDetail);
            }
            dataReader.Close();
            return assetRequisitionDetailsList;

        }

        /// <summary>
        /// Construct SP_Command
        /// </summary>
        /// <param name="command"></param>
        /// <param name="assetRequisitionDetail"></param>
        /// <returns></returns>
        public SqlCommand ConstructCommand(SqlCommand command, AssetRequisitionDetail assetRequisitionDetail)
        {
            command.CommandType = CommandType.StoredProcedure;
            try
            {
                command.Parameters.AddWithValue("@TransactionId", assetRequisitionDetail.TransactionId);
            }
            catch (Exception)
            {
            }
            try
            {
                command.Parameters.AddWithValue("@RequestingOrganizationLevelId",
                                                assetRequisitionDetail.RequestingOrganizationLevelId);
            }
            catch (Exception)
            {
            }
            try
            {
                command.Parameters.AddWithValue("@IssuingOrganizationLevelId",
                                                assetRequisitionDetail.IssuingOrganizationLevelId);
            }
            catch (Exception)
            {
            }
            try
            {
                command.Parameters.AddWithValue("@RequestingEmpId", assetRequisitionDetail.RequestingEmpId);
            }
            catch (Exception)
            {
            }
            try
            {
                command.Parameters.AddWithValue("@AssetId", assetRequisitionDetail.AssetId);
            }
            catch (Exception)
            {
            }
            try
            {
                command.Parameters.AddWithValue("@Quantity", assetRequisitionDetail.Quantity);
            }
            catch (Exception)
            {
            }
            try
            {
                command.Parameters.AddWithValue("@Status", assetRequisitionDetail.Status);
            }
            catch (Exception)
            {
            }
            try
            {
                command.Parameters.AddWithValue("@UnitOfMeasure", assetRequisitionDetail.UnitOfMeasure);
            }
            catch (Exception)
            {
            }
            try
            {
                command.Parameters.AddWithValue("@TransactionDate", assetRequisitionDetail.TransactionDate);
            }
            catch (Exception)
            {
            }
            return command;
        }

    }
}

